Excel BI - Excel Challenge 746

excel-challenges

excel-formulas

🔰 Answer Expected Col1 Col2 Col3 Col4 Col5 Col6 A1 D1 B1

Published

March 24, 2026

Illustration for Excel BI - Excel Challenge 746

Challenge Description

🔰 Answer Expected Col1 Col2 Col3 Col4 Col5 Col6 A1 D1 B1

Solutions

library(tidyverse)
library(readxl)

path = "Excel/700-799/746/746 Alignment of Data.xlsx"
input = read_excel(path, range = "A2:B16")
test  = read_excel(path, range = "D2:I8")

i2 = c(input$Col1, input$Col2) %>% na.omit() %>%
  data.frame(x = .) %>%
  mutate(dig = str_extract(x, "\\d+") %>% as.numeric()) %>%
  complete(dig = min(dig):max(dig), fill = list(x = NA)) %>%
  summarise(Col = ifelse(is.na(x), NA, paste0(x, collapse = ", ")), .by = dig) %>%
  distinct() %>%
  separate_wider_delim(Col, delim = ", ", names_sep = "", too_few = "align_start") %>%
  select(-dig)

all.equal(i2, test, check.attributes = FALSE)
# [1] TRUE

Logic: Read the workbook ranges needed for the challenge; Derive the required intermediate columns; Parse the packed text or string structure; Aggregate or rank the data at the required grouping level.
Strengths: The solution stays close to the text pattern itself, which makes the extraction logic easy to audit.
Areas for Improvement: The solution assumes the workbook layout and selected ranges remain stable, so any structural change in the sheet would require small adjustments.
Gem: A small number of well-targeted text patterns does most of the heavy lifting.

import pandas as pd
import numpy as np
import re

path = "700-799/746/746 Alignment of Data.xlsx"
input = pd.read_excel(path, usecols="A:B", skiprows=1, nrows=15)
test = pd.read_excel(path, usecols="D:I", skiprows=1, nrows=7).rename(columns=lambda c: re.sub(r'\.1$', '', c))
i2 = pd.concat([input.iloc[:,0], input.iloc[:,1]]).dropna().astype(str)
digs = i2.str.extract(r'(\d+)').astype(float)[0]
all_digs = pd.Series(np.arange(int(digs.min()), int(digs.max())+1), name='dig')
grouped = pd.DataFrame({'dig': digs.values, 'x': i2.values}).groupby('dig')['x'].apply(lambda xs: ', '.join(xs)).reindex(all_digs).reset_index()
split_cols = grouped['x'].str.split(', ', expand=True)
split_cols.columns = [f'Col{i+1}' for i in range(split_cols.shape[1])]
result = split_cols

print(result.equals(test))

The Python version follows the same grouped logic and keeps the transformation explicit in a dataframe pipeline.

Difficulty Level

Medium

The individual steps are manageable, but the correct transformation pattern is not obvious from the raw data.